package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;

public class DBPerson 
{
	private Connection con;
	private DBUdlej_Salg_Vare dbUsv;
	
	private static DBPerson   instance = null;
    
    public static DBPerson getInstance()
    {
        if (instance == null)
        {
          instance = new DBPerson();
        }
        return instance;
    }
	
	public DBPerson()
	{
		con = DBConnection.getInstance().getDBcon();
		dbUsv = DBUdlej_Salg_Vare.getInstance();
	}
	
	private void create(String wClause)
	{
	    String query="INSERT INTO Person ";

		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			
			try
			{
				stmt = con.createStatement(); 
				stmt.executeUpdate(query); 
				con.commit(); 
				stmt.close();
			}
			catch (Exception e)
			{ 
				e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
	
	public void createPerson(Person p)
	{
		String wClause = "VALUES " 
	        + "('" + p.getNavn()  + "',"
	        + "'" + p.getAdresse() + "',"
			+ "'" + p.getEmail() + "',"
			+ "'" + p.getCprnr() + "',"
			+ "'" + p.getTlfnr() + "',"
			+ "" + p.getRabat().getRabatid() + ","
			+ "" + p.getPostnr().getPostnrbyid() + ")";
	        create(wClause);
	}
	
	public void updatePerson(Person person)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		   ("UPDATE Person SET " 
				+ "Navn = '" + person.getNavn() + "',"
		   		+ "adresse = '" + person.getAdresse() + "',"
		   		+ "Email = '" + person.getEmail() + "',"
		   		+ "Cprnr = '" + person.getCprnr() + "',"
		   		+ "Tlfnr = '" + person.getTlfnr() + "',"
		   		+ "Rabatid = " + person.getRabat().getRabatid() + ","
		   		+ "PostNrby = " + person.getPostnr().getPostnrbyid() + ""
		   	+ " WHERE PersonId = " + person.getPersonid() + "");
		  prepstmt.executeUpdate();
		             
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	}
	
	public void deletePerson(int personId)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Person WHERE personId = " + personId + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	} 
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Person";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Person singleWhere(String wClause, boolean retrieveAssocation)
	{
		ResultSet results;
		Person person = new Person();
	    String query =  buildQuery(wClause);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            person = buildPerson(results, retrieveAssocation);
                            stmt.close();
			}
		
		}
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return person;
	}
	
	public ArrayList<Person> getAllePersoner()
	{
		return miscWhere("");
	}
	
	private ArrayList<Person> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Person cusObj = new Person();
		     	 cusObj = buildPerson(results, true);	
                 list.add(cusObj);	
			}
			stmt.close();

		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private Person buildPerson(ResultSet results, boolean retrieveAssocation)
    {   
		DBPostnrby dbPost = new DBPostnrby();
		DBRabat dbRab = new DBRabat();
		Person person = new Person();
          try
          {
        	  	person.setPersonid(results.getInt(1));
                person.setNavn(results.getString(2));
                person.setAdresse(results.getString(3));
                person.setEmail(results.getString(4));
                person.setCprnr(results.getString(5));
                person.setTlfnr(results.getString(6));
                person.setRabat(dbRab.findRabat(results.getInt(7)));
                person.setPostnr(dbPost.findPostnrbyId(results.getInt(8), true));
                if(retrieveAssocation)
                {
                	person.setUsv(dbUsv.usvViaKunde(results.getInt(1)));
                }
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the Person object");
         }
         return person;
    }
    
	public Person findPersonCprnr(int cprnr, boolean retrieveAssocation) 
    {
    	String wClause = "  cprnr = " + Integer.toString(cprnr) + "";
    	return singleWhere(wClause,retrieveAssocation);
    }
	public Person findPersonTlfnr(String tlfnr, boolean retrieveAssocation) 
    {
    	String wClause = "  tlfnr = " + (tlfnr) + "";
    	return singleWhere(wClause,retrieveAssocation);
    }
	
	public Person findPersonId(int id, boolean retrieveAssocation)
	{
		String wClause = " personid = " + Integer.toString(id) + "";
		return singleWhere(wClause,retrieveAssocation);
	}
}
